package com.wondersgroup.core.util;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.CommentsTable;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRElt;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

import com.wondersgroup.core.frame.TraceException;

/**
 * 使用event API读取Excel2007及之后版本的excel文档(xlsx后缀)
 * 
 * @author wanglei
 */
public class XSSFEventusermodelReader {

    private static final Logger logger = Logger.getLogger(XSSFEventusermodelReader.class.getName());
    private List<List<List<String>>> readedWorkbookData; //读取到的所有单元格内容
    private List<Map<String, String>> readedCommentData; //读取到的所有comment数据
    private String[] assignSheetNames;
    private int[] assignSheetIndexs;
    private static final boolean isPrintLog = false;

    private static void printLog(Level level, String log){
        if (isPrintLog) logger.log(level, log);
    }
    private static void printLog(Level level, String log, Object par){
        if (isPrintLog) logger.log(level, log, par);
    }
    private static void printLog(Level level, String log, Object[] pars){
        if (isPrintLog) logger.log(level, log, pars);
    }
    private static void printLog(Level level, String log, Throwable throwable){
        if (isPrintLog) logger.log(level, log, throwable);
    }
    
    /**
     * 单元格中的数据可能的数据类型
     *
     */
    enum DataType {
        BOOL,
        ERROR,
        FORMULA,
        INLINESTR,
        SSTINDEX,
        NUMBER,
        DATE,
        NULL
    }

    public class XSSFEventusermodelSheetHandler extends DefaultHandler {
        private final SharedStringsTable sst;
        private final StylesTable stylesTable;
        private CommentsTable commentsTable;
        
        private String lastContents = "";
        private boolean isTElement = false;
        private List<String> curRowData;
        private List<List<String>> curSheetData;
        //当前行
        private int curRow = 0;
        private int curCol = 0;
        //标识当前节点是单元格内容
        private boolean vIsOpen = false;
        // Set when cell start element is seen;  
        // used when cell close element is seen.  
        private DataType nextDataType;
        //数值类型单元格的格式设置
        private short formatIndex;
        private String formatString;
        private final DataFormatter formatter;
        //当前sheet内的comment内容
        private Map<String, String> curSheetComments;

        public XSSFEventusermodelSheetHandler(SharedStringsTable sst, StylesTable stylesTable) {
            this.sst = sst;
            this.stylesTable = stylesTable;
            this.formatter = new DataFormatter();
            this.nextDataType = DataType.NUMBER;
        }

        private void addRowData(String data, int row, int column) {
            if(curRowData==null){
                curRowData = new ArrayList<>();
            }
            if(column > curRowData.size()){
                int len = column - curRowData.size();
                for(int i=0; i<len; i++){
                    curRowData.add(null);
                }
            }
            curRowData.add(data);
            curCol++;
        }

        private void enterNextRow(int row) {
            curRowData = new ArrayList<>();
            if(curSheetData!=null){
                if(row > curSheetData.size()){
                    //中间有被忽略的空白行
                    int len = row - curSheetData.size();
                    for(int i=0; i<len; i++){
                        curSheetData.add(new ArrayList<String>());
                    }
                }
                curSheetData.add(curRowData);
            }
            curRow = row;
            curCol = 0;
        }
        
        /**
         * 进入下一个sheet之前的清理工作
         * @param curSheetData
         * @param curSheetComments
         * @param commentsTable
         */
        public void enterNewSheet(List<List<String>> curSheetData, Map<String, String> curSheetComments, 
                CommentsTable commentsTable) {
            this.commentsTable = commentsTable;
            lastContents = "";
            isTElement = false;
            curRowData = null;
            this.curSheetData = curSheetData;
            curRow = 0;
            curCol = 0;
            vIsOpen = false;
            nextDataType = DataType.NUMBER;
            formatIndex = -1;
            formatString = null;
            this.curSheetComments = curSheetComments; 
        }
        
        /**
         * 在读取元素开始时的处理，
         *
         * @param uri
         * @param localName
         * @param name
         * @param attributes
         * @throws SAXException
         */
        @Override
        public void startElement(String uri, String localName, String name,
                Attributes attributes) throws SAXException {
            isTElement = false;
            if (null != name) {
                switch (name) {
                    case "inlineStr":
                    case "v":
                        vIsOpen = true;
                        break;
                    // c => 单元格
                    case "c":
                        //获取单元格位置计算单元格在哪一列 
                        String r = attributes.getValue("r");
                        int firstDigit = -1;
                        for (int c = 0; c < r.length(); ++c) {
                            if (Character.isDigit(r.charAt(c))) {
                                firstDigit = c;
                                break;
                            }
                        }
                        //curRow = Integer.valueOf(r.substring(firstDigit)) - 1;
                        curCol = nameToColumn(r.substring(0, firstDigit));
                        //获取注释内容
                        if (commentsTable!=null){
                            CTComment ctc = commentsTable.getCTComment(r);
                            if(ctc!=null && ctc.getText()!=null){
                                String comment = null;
                                String author = commentsTable.getAuthor(ctc.getAuthorId());
                                List<CTRElt> rList = ctc.getText().getRList();
                                if(rList!=null && !rList.isEmpty()) {
                                    comment = "";
                                    for(CTRElt elt : rList){
                                        comment += elt.getT();
                                    }
                                }
                                if(comment!=null && curSheetComments!=null){
                                    String key = curRow + "_" + curCol;
                                    curSheetComments.put(key, comment);
                                }
                                printLog(Level.INFO, "Comment found with value {0} at row {1} and column {2} author {3}", new Object[]{comment, curRow, curCol, author});
                            }
                        }
                        
                        // 记录数据类型
                        dealDataType(attributes);
                        break;
                    // 当元素为t时
                    case "t":
                        isTElement = true;
                        break;
                    // 到达行的开头
                    case "row":
                        // 获取行号
                        String rowRef = attributes.getValue("r");
                        int rowNum = Integer.valueOf(rowRef) - 1;
                        enterNextRow(rowNum);
                        break;
                }
            }
            // 清空旧的单元格内容
            lastContents = "";
        }

        @Override
        public void endElement(String uri, String localName, String name)
                throws SAXException {
            //t元素也包含字符串
            if (isTElement) {
                String value = lastContents;
                addRowData(value, curRow, curCol);
                isTElement = false;
            // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
            } else if ("v".equals(name)) {
                String value = dealData(lastContents);
                addRowData(value, curRow, curCol);
                vIsOpen = false;
            // 计算公式内容
            } else if ("f".equals(name)) {
            } else {
                //如果标签名称为 row ，这说明已到行尾
                if (name.equals("row")) {
                    //enterRowEnd();
                }
            }
        }

        @Override
        public void characters(char[] ch, int start, int length)
                throws SAXException {
            if (vIsOpen){
                lastContents += new String(ch, start, length);
            }
        }

        /**
         * 处理数据类型, 日期和数值都视为数值型
         *
         * @param attributes
         */
        public void dealDataType(Attributes attributes) {
            this.formatIndex = -1;
            this.formatString = null;
            String cellType = attributes.getValue("t");
            String cellStyleStr = attributes.getValue("s");
            nextDataType = DataType.NUMBER;
            if ("b".equals(cellType)) {
                nextDataType = DataType.BOOL;
            } else if ("e".equals(cellType)) {
                nextDataType = DataType.ERROR;
            } else if ("inlineStr".equals(cellType)) {
                nextDataType = DataType.INLINESTR;
            } else if ("s".equals(cellType)) {
                nextDataType = DataType.SSTINDEX;
            } else if ("str".equals(cellType)) {
                nextDataType = DataType.FORMULA;
            } else if (cellStyleStr != null) {
                // 数值型，获取单元格内定义的显示格式
                nextDataType = DataType.NUMBER;
                int styleIndex = Integer.parseInt(cellStyleStr);
                XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
                this.formatIndex = style.getDataFormat();
                this.formatString = style.getDataFormatString();
                if (this.formatString == null) {
                    this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
                }
            }
        }

        /**
         * 对解析出来的数据进行类型处理
         *
         * @param value 单元格的值（这时候是一串数字）
         * @return
         */
        public String dealData(String value) {
            String cellData = null;
            switch (nextDataType) {
                case BOOL:
                    char first = value.charAt(0);
                    cellData = first == '0' ? "false" : "true";  
                    printLog(Level.INFO, "Boolean cell found with value {0} at row {1} and column {2}", new Object[]{cellData, curRow, curCol});
                    break;
                case ERROR:
                    cellData = value;
                    printLog(Level.INFO, "Error cell found with value {0} at row {1} and column {2}", new Object[]{cellData, curRow, curCol});
                    break;
                case FORMULA:
                    cellData = value;
                    printLog(Level.INFO, "Formula cell found with value {0} at row {1} and column {2}", new Object[]{cellData, curRow, curCol});
                    break;
                case INLINESTR:
                    XSSFRichTextString rtsi = new XSSFRichTextString(value);
                    cellData = rtsi.toString();
                    printLog(Level.INFO, "InlineStr cell found with value {0} at row {1} and column {2}", new Object[]{cellData, curRow, curCol});
                    break;
                case SSTINDEX:
                    try {
                        int idx = Integer.parseInt(value);
                        XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));
                        cellData = rtss.toString();
                        printLog(Level.INFO, "String cell found with value {0} at row {1} and column {2}", new Object[]{cellData, curRow, curCol});
                    } catch (NumberFormatException ex) {
                        printLog(Level.INFO, "Failed to parse SST index " + value, ex);  
                        cellData = value;
                    }
                    break;
                case NUMBER:
                    cellData = value;
                    String numberValue = null;
                    if (this.formatString != null){
                        numberValue = formatter.formatRawCellContents(Double.parseDouble(value), this.formatIndex, this.formatString);  
                    } else { 
                        numberValue = value;
                    }
                    printLog(Level.INFO, "Number Cell found with value {0} at row {1} and column {2}", new Object[]{numberValue, curRow, curCol});
                    break;
                case DATE:
                    cellData = value;
                    // 对日期字符串作特殊处理
                    String dateStr = null;
                    if (this.formatString != null){
                        dateStr = formatter.formatRawCellContents(Double.parseDouble(value), this.formatIndex, this.formatString);  
                    } else { 
                        dateStr = value;
                    }
                    printLog(Level.INFO, "Date Cell found with value {0} at row {1} and column {2}", new Object[]{dateStr, curRow, curCol});
                    break;
                default:
                    printLog(Level.INFO, "Unexpected type: {0} at row {1} and column {2}", new Object[]{nextDataType, curRow, curCol});
                    cellData = value;
                    break;
            }
            return cellData;
        }

        /**
         * Converts an Excel column name like "C" to a zero-based index.
         *
         * @param name
         * @return Index corresponding to the specified name
         */
        private int nameToColumn(String name) {
            int column = -1;
            for (int i = 0; i < name.length(); ++i) {
                int c = name.charAt(i);
                column = (column + 1) * 26 + c - 'A';
            }
            return column;
        }
    }

    private XMLReader fetchSheetParser() throws SAXException, ParserConfigurationException {
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        SAXParser saxParser = saxFactory.newSAXParser();
        XMLReader sheetParser = saxParser.getXMLReader();
        return sheetParser;
    }

    private void processSheet(XSSFReader.SheetIterator iter, XSSFEventusermodelSheetHandler handler, 
            InputStream sheet, XMLReader sheetParser) throws IOException, SAXException{
        CommentsTable commentsTable = iter.getSheetComments();
        List<List<String>> sheetData = new ArrayList<>();
        readedWorkbookData.add(sheetData);
        Map<String, String> sheetComments = new HashMap<>();
        readedCommentData.add(sheetComments);
        handler.enterNewSheet(sheetData, sheetComments, commentsTable);
        InputSource sheetSource = new InputSource(sheet);
        sheetParser.parse(sheetSource);
        sheet.close();
    }
    
    private List<List<List<String>>> readWorkbook(InputStream is)
            throws InvalidFormatException, IOException, OpenXML4JException, SAXException, ParserConfigurationException {
        try {
            readedWorkbookData = new ArrayList<>();
            readedCommentData = new ArrayList<>();
            
            OPCPackage pkg = OPCPackage.open(is);
            XSSFReader r = new XSSFReader(pkg);
            SharedStringsTable sst = r.getSharedStringsTable();
            StylesTable styles = r.getStylesTable();
            
            XMLReader sheetParser = fetchSheetParser();
            XSSFEventusermodelSheetHandler handler = new XSSFEventusermodelSheetHandler(sst, styles);
            sheetParser.setContentHandler(handler);
            XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) r.getSheetsData();
            int sheetNo = 0;
            
            printLog(Level.INFO, "Encountered workbook");
            while (iter.hasNext()) {
                InputStream sheet = iter.next();
                String sheetName = iter.getSheetName();  
                
                boolean ignoreCurSheet = false;
                if (assignSheetIndexs != null && assignSheetIndexs.length > 0) {
                    ignoreCurSheet = true;
                    for (int index : assignSheetIndexs) {
                        if (index == sheetNo) {
                            ignoreCurSheet = false;
                            break;
                        }
                    }
                }else if(assignSheetNames!=null && assignSheetNames.length > 0  && sheetName!=null) {
                    ignoreCurSheet = true;
                    for (String name : assignSheetNames) {
                        if (name!=null && name.equals(sheetName)) {
                            ignoreCurSheet = false;
                            break;
                        }
                    }
                }
                printLog(Level.INFO, "New sheet named: {0}", sheetName);
                
                if(!ignoreCurSheet){
                    processSheet(iter, handler, sheet, sheetParser);
                }
                sheetNo++;
            }
            printLog(Level.INFO, "done.");
        } catch (InvalidFormatException | IOException ex) {
            printLog(Level.SEVERE, null, ex);
            throw ex;
        } catch (OpenXML4JException ex) {
            printLog(Level.SEVERE, null, ex);
            throw ex;
        } catch (SAXException | ParserConfigurationException ex) {
            printLog(Level.SEVERE, null, ex);
            throw ex;
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
            } catch (IOException ex) {
                printLog(Level.SEVERE, null, ex);
            }
        }
        
        return readedWorkbookData;
    }
    
    /**
     * 读取xlsx文件，指定assignSheetIndexs后会忽略其他sheet的内容
     * @param is
     * @param assignSheetIndexs
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     * @throws OpenXML4JException
     * @throws SAXException
     * @throws ParserConfigurationException 
     */
    public List<List<List<String>>> readByIndex(InputStream is, int... assignSheetIndexs)
            throws InvalidFormatException, IOException, OpenXML4JException, SAXException, ParserConfigurationException {
        this.assignSheetIndexs = assignSheetIndexs;
        this.assignSheetNames = null;
        return readWorkbook(is);
    }
    
    /**
     * 读取xlsx文件，指定assignSheetNames后会忽略其他sheet的内容
     * @param is
     * @param assignSheetNames
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     * @throws OpenXML4JException
     * @throws SAXException
     * @throws ParserConfigurationException 
     */
    public List<List<List<String>>> readByName(InputStream is, String... assignSheetNames)
            throws InvalidFormatException, IOException, OpenXML4JException, SAXException, ParserConfigurationException {
        this.assignSheetIndexs = null;
        this.assignSheetNames = assignSheetNames;
        return readWorkbook(is);
    }
    
    /**
     * 删掉数值类型中多余的0
     *
     * @param s
     * @return
     */
    public static String removeZeroBorder(String s) {
        if (s != null && !"".equals(s)) {
            //replace xxx.000 to xxx
            String result = s;
            Pattern pattern = Pattern.compile("\\.0+\\b");
            Matcher matcher = pattern.matcher(result);
            if (matcher.find()) {
                result = matcher.replaceAll("");
            }

            //replace xxx.xxx000 to xxx.xxx
            pattern = Pattern.compile("\\-?\\d+\\.0*[^0]+");
            matcher = pattern.matcher(result);
            if (matcher.find()) {
                result = matcher.group(0);
            }

            return result;
        }

        return null;
    }

    /**
     * 处理数值型数据小数点后的保留位数, 会删除多余的0
     * @param value
     * @param scale
     * @return
     */
    public static String processNumericCellValue(double value, Integer scale) {
        if(scale!=null){
            StringBuilder s = new StringBuilder("0");
            if (scale > 0) {
                s.append(".");
                for (int i = 0; i < scale; i++) {
                    s.append("0");
                }
            }
            DecimalFormat format = new DecimalFormat(s.toString());
            String result = format.format(value);

            //remove zero border
            result = removeZeroBorder(result);
            return result;
        }else{
            return String.valueOf(value);
        }
    }
    
    /**
     * 将读取到的double类型值转换为日期类型
     *
     * @param daynum
     * @return
     */
    public static Date getDate(double daynum) {
        Date date = null;
        if (HSSFDateUtil.isValidExcelDate(daynum)) {
            date = HSSFDateUtil.getJavaDate(daynum);
        }
        return date;
    }
    
    /**
     * 处理计时类型的数据 [正确地处理整数后自动加零的情况]
     *
     * @param daynum
     * @return
     */
    public static String getTime(double daynum) {
        double totalSeconds = daynum * 86400.0D;
        //总的分钟数  
        int seconds = (int) totalSeconds / 60;
        //实际小时数  
        int hours = seconds / 60;
        int minutes = seconds - hours * 60;
        //剩余的实际分钟数  
        StringBuilder sb = new StringBuilder();
        if (String.valueOf(hours).length() == 1) {
            sb.append("0").append(hours);
        } else {
            sb.append(hours);
        }
        sb.append(":");
        if (String.valueOf(minutes).length() == 1) {
            sb.append("0").append(minutes);
        } else {
            sb.append(minutes);
        }
        return sb.toString();
    }

    public List<List<List<String>>> getReadedWorkbookData() {
        return readedWorkbookData;
    }

    public List<Map<String, String>> getReadedCommentData() {
        return readedCommentData;
    }
    
    /**
     * 重载  getCellData：从行数据中获取指定列单元格的数据
     * @param rowData
     * @param row
     * @param col
     * @param require
     * @param clazz
     * @param dateFormat
     * @return
     * @throws ParseException
     */
    public static Object getCellData(List<String> rowData, int row, int col, boolean require, Class<?> clazz, String dateFormat) throws ParseException{
    	return getCellData(rowData, row, col, require, clazz, dateFormat, false, false);
    }
    
    /**
     * 从行数据中获取指定列单元格的数据
     * @param rowData
     * @param row
     * @param col
     * @param require 是否必填
     * @param clazz 返回值类型
     * @param dateFormat 日期格式
     * @return 
     * @throws java.text.ParseException 
     */
    public static Object getCellData(List<String> rowData, int row, int col, boolean require, Class<?> clazz, String dateFormat,boolean trimAll,boolean removeBrackets) throws ParseException{
        if(rowData==null || (rowData.size()<=col && require)){
            //无法获取到数据
            throw TraceException.returnExceptionWithPars("004012", row + 1, col + 1);
        }
        String str = null;
        if(col<0){
            return null;
        }
        if(rowData.size()>col){
            str = rowData.get(col);
        }
        Object value = null;
        if(clazz.equals(String.class)){
        	str = QJConvert.qj2bj(str);
        	if(trimAll) {
        		str = EncodeUtils.removeAllSpace(str);
        	}
        	if(removeBrackets) {
        		str = EncodeUtils.removeBrackets(str);
        	}
            return str;
        }else{
            if(require && (str==null || str.isEmpty())){
                //必填值获取不到
                throw TraceException.returnExceptionWithPars("004012", row + 1, col + 1);
            }else if(!require && (str==null || str.isEmpty())){
                //非必填同时无值
                value = null;
            }else{
                //日期型
                try {
                    if(clazz.equals(Date.class)){
                        try {
                            //如果取到的是数值
                            value = getDate(Double.valueOf(str));
                        } catch (NumberFormatException e) {
                            //如果取到的是字符串
                            DateFormat format = new SimpleDateFormat(dateFormat);
                            value = format.parse(str);
                        }
                    }else if(clazz.equals(Integer.class)){
                        value = Integer.valueOf(str);
                    }else if(clazz.equals(Double.class)){
                        value = Double.valueOf(str);
                    }else if(clazz.equals(Boolean.class)){
                        value = Boolean.valueOf(str);
                    }else if(clazz.equals(BigDecimal.class)){
                        value = new BigDecimal(str);
                    }else if(clazz.equals(Byte.class)){
                        value = Byte.valueOf(str);
                    }else if(clazz.equals(Long.class)){
                        value = Long.parseLong(str);
                    }
                } catch (ParseException | NumberFormatException e) {
                    //数据格式错误
                    throw TraceException.returnExceptionWithPars("004012", row + 1, col + 1);
                }
            }
        }
        return value;
    }
}
